package com.flyzing.genl.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.flyzing.genl.po.ColumnPO;
import com.flyzing.genl.po.TablePO;
import com.flyzing.genl.util.StringUtil;

/**
 * @author yaoguangyao
 * @Date 2020年12月30日
 */
public class TablesDAO {
	private DBConnFactory factory;
	
	public TablesDAO(DBConnFactory factory) {
		if (factory == null) {
			throw new RuntimeException("DBConnFactory不能为空");
		}
		this.factory = factory;
	}
	
	public List<TablePO> getTablesByNames(String schema, String[] tableNames) {
		List<TablePO> result = new ArrayList<>();
		String sql = "select "
				+ " table_schema, table_name, table_comment "
				+ " from information_schema.tables "
				+ " where table_schema=? and table_name in (";
		for (int i = 0; i < tableNames.length; i++) {
			if (StringUtil.isNotNull(tableNames[i])) {
				sql = sql + "'" + tableNames[i].trim() + "'";
				if (i < tableNames.length - 1) {
					sql += ",";
				}
			}
		}
		sql += ")";
		
		Connection conn = factory.create();
		if (conn != null) {
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				ps = conn.prepareStatement(sql);
				ps.setString(1, schema.trim());
				rs = ps.executeQuery();
				TablePO table = null;
				while(rs.next()) {
					table = new TablePO();
					table.setTableSchema(rs.getString("table_schema"));
					table.setTableName(rs.getString("table_name"));
					table.setTableComment(rs.getString("table_comment"));
					result.add(table);
				}
			} catch (SQLException e) {
				throw new RuntimeException("sql执行错误，sql: " + sql);
			} finally {
				factory.close(conn, ps, rs);
			}
		}
		
		return result;
	}

	public List<TablePO> getTablesByPrefix(String schema, String tablePrefix) {
		List<TablePO> result = new ArrayList<>();
		
		String sql = "select "
				+ " table_schema, table_name, table_comment "
				+ " from information_schema.tables "
				+ " where table_schema=? and table_name like ?";
		Connection conn = factory.create();
		if (conn != null) {
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				ps = conn.prepareStatement(sql);
				ps.setString(1, schema.trim());
				ps.setString(2, tablePrefix.trim() + "%");
				rs = ps.executeQuery();
				TablePO table = null;
				while(rs.next()) {
					table = new TablePO();
					table.setTableSchema(rs.getString("table_schema"));
					table.setTableName(rs.getString("table_name"));
					table.setTableComment(rs.getString("table_comment"));
					result.add(table);
				}
			} catch (SQLException e) {
				throw new RuntimeException("sql执行错误，sql: " + sql);
			} finally {
				factory.close(conn, ps, rs);
			}
		}
		
		return result;
	}
	
	public List<ColumnPO> getColumnsByTableName(TablePO table) {
		List<ColumnPO> result = new ArrayList<>();
		String sql = "select table_schema, table_name, column_name,"
				+ " data_type, column_comment, column_key"
				+ " from information_schema.columns "
				+ " where table_schema=? and table_name=? order by ordinal_position";
		Connection conn = factory.create();
		PreparedStatement ps = null;
		ResultSet rs = null;
		if (conn != null) {
			try {
				ps = conn.prepareStatement(sql);
				ps.setString(1, table.getTableSchema());
				ps.setString(2, table.getTableName());
				
				rs = ps.executeQuery();
				ColumnPO column = null;
				while(rs.next()) {
					column = new ColumnPO();
					column.setTableName(rs.getString("table_schema"));
					column.setTableName(rs.getString("table_name"));
					column.setColumnName(rs.getString("column_name"));
					column.setDataType(rs.getString("data_type"));
					column.setColumnComment(rs.getString("column_comment"));
					column.setColumnKey(rs.getString("column_key"));
					result.add(column);
				}
			} catch (SQLException e) {
				throw new RuntimeException("sql执行错误，sql: " + sql);
			} finally {
				factory.close(conn, ps, rs);
			}
		}
		
		return result;
	}
}
